package cc.rengu.redp.bizimpl.upmp.controller;


import cc.rengu.redp.bizimpl.upmp.bean.ImportExcelColumnAttr;
import cc.rengu.redp.bizimpl.upmp.bean.MchntImportExportInitNew;
import cc.rengu.redp.bizimpl.upmp.domain.*;
import cc.rengu.redp.bizimpl.upmp.service.*;
import cc.rengu.redp.bizimpl.upmp.vo.BTermBrandInfoVO;
import cc.rengu.redp.bizimpl.upmp.vo.BTermInfoVO;
import cc.rengu.redp.bizimpl.utils.CommonUtils;
import cc.rengu.redp.bizimpl.utils.ExcelOperaUtil;
import cc.rengu.redp.bizimpl.utils.ExtPropertyPlaceholderConfigurer;
import cc.rengu.redp.common.annotation.Log;
import cc.rengu.redp.common.controller.BaseController;
import cc.rengu.redp.common.domain.QueryRequest;
import cc.rengu.redp.common.domain.RedpResponse;
import cc.rengu.redp.common.exception.RedpException;
import cc.rengu.redp.common.utils.EasyExcelUtil;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.google.common.collect.ImmutableMap;
import com.google.common.collect.Lists;
import com.wuwenze.poi.pojo.ExcelErrorField;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import sun.misc.BASE64Encoder;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.IntStream;

/**
 * @author lizk
 */
@RestController
@Slf4j
@RequestMapping("info")
public class BTermInfoController extends BaseController {

    @Autowired
    private IBTermInfoService ibTermInfoService;
    @Autowired
    private IBDeviceInfoService ibDeviceInfoService;
    @Autowired
    private RegionInfoService regionInfoService;
    @Autowired
    private IBTermBrandInfoService ibTermBrandInfoService;
    @Autowired
    private IBTermModelInfoService ibTermModelInfoService;
    @Autowired(required = false)
    private ExtPropertyPlaceholderConfigurer extPropertyPlaceholderConfigurer;

    String[] provinces;
    String[] brandCodes;
    String[] snLst;


    @GetMapping
    public Map<String, Object> brandList(QueryRequest queryRequest, BTermInfoVO termInfo) {
        return getDataTable(ibTermInfoService.findByPage(queryRequest, termInfo));
    }

    @DeleteMapping("resetKey")
    public void resetKey(@Valid BTermInfoVO info) throws RedpException {
        ibTermInfoService.resetKey(info);
    }

    @GetMapping("getSN")
    public List<BDeviceInfo> getSN(@RequestParam("instId") String instId) {
        return ibDeviceInfoService.getSN(instId);
    }

    @GetMapping("snSelect2List")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "query", name = "q", value = "参数", required = true, dataType = "String")
    })
    public Map<String, Object> snSelect2List(String q, QueryRequest request){
        return ibDeviceInfoService.snSelect2List(q, request);
    }


    @Log("新增间联信息")
    @PostMapping
    public void addInfo(@Valid BTermInfoVO info) throws RedpException {
        try {
            this.ibTermInfoService.createInfo(info);
        } catch (Exception e) {
            String message = e.getMessage();
            log.error(message, e);
            throw new RedpException(message);
        }
    }

    @Log("修改间联信息")
    @PutMapping
//    @RequiresPermissions("brand:update")
    public void updateBland(BTermInfoVO info) throws RedpException {
        try {
            this.ibTermInfoService.updateInfo(info);
        } catch (Exception e) {
            String message = e.getMessage();
            log.error(message, e);
            throw new RedpException(message);
        }
    }

    @Log("间联信息注销/停用/使用")
    @PutMapping("infoStatusChange")
    public void infoStatusChange(BTermInfoVO info) throws RedpException {
        try {
            this.ibTermInfoService.infoStatusChange(info);
        } catch (Exception e) {
            String message = e.getMessage();
            log.error(message, e);
            throw new RedpException(message);
        }
    }


    /**
     * 导入Excel数据，并批量插入 T_B_TERM_INFO表
     */
    @PostMapping("import")
    public RedpResponse importExcels(@RequestParam("file") MultipartFile file,
                                     @RequestParam("instId") String instId) throws RedpException {

        try {
            if (file.isEmpty()) {
                throw new RedpException("导入数据为空");
            }
            String filename = file.getOriginalFilename();
            if (!StringUtils.endsWith(filename, ".xlsx")) {
                throw new RedpException("只支持.xlsx类型文件导入");
            }

            // 开始导入操作
            long beginTimeMillis = System.currentTimeMillis();
            final List<BTermInfoVO> data = Lists.newArrayList();
            final List<Map<String, Object>> error = Lists.newArrayList();

            XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
            XSSFSheet sheet = workbook.getSheetAt(0);
            Row row;
            int realRows = CommonUtils.findRealRows(sheet);
            for (int i = 1; i < realRows; i++) {
                row = sheet.getRow(i); // 获取行
                BTermInfoVO bTermInfoVO = new BTermInfoVO();
                // count++; //按行的话

                bTermInfoVO.setMchntNo(getCellValue(row, 0));
                bTermInfoVO.setTermType(getCellValue(row, 1));
                bTermInfoVO.setBrandCode(getCellValue(row, 2));
                bTermInfoVO.setModelCode(getCellValue(row, 3));
                bTermInfoVO.setStartTime(getCellValue(row, 4));
                bTermInfoVO.setTermTxnStarWay(getCellValue(row, 5));
                bTermInfoVO.setTermDevcTp(getCellValue(row, 6));
                bTermInfoVO.setTermAddrPrvnc(getCellValue(row, 8));
                bTermInfoVO.setTermAddrCity(getCellValue(row, 9));
                bTermInfoVO.setTermAddrArea(getCellValue(row, 10));
                bTermInfoVO.setShopAttr(getCellValue(row, 11));
                bTermInfoVO.setShoptime(getCellValue(row, 12));
                bTermInfoVO.setSn(getCellValue(row, 13));
                bTermInfoVO.setContactName(getCellValue(row, 14));
                bTermInfoVO.setContactPhone(getCellValue(row, 15));
                bTermInfoVO.setLongitude(getCellValue(row, 16));
                bTermInfoVO.setLatitude(getCellValue(row, 17));
                bTermInfoVO.setEffectiveRadius(getCellValue(row, 18));

                //数据校验和处理
                bTermInfoVO = ibTermInfoService.checkAndDeal(bTermInfoVO);
                List<ExcelErrorField> list = new ArrayList<>();

                if ("".equals(bTermInfoVO.getCheckFlag()) || null == bTermInfoVO.getCheckFlag()) {//不存在
                    // 数据校验成功时，加入集合
                    bTermInfoVO.setInstId(instId);
                    data.add(bTermInfoVO);
                } else {
                    //把品牌名称重复的放置到错误信息里面
                    list.add(new ExcelErrorField(bTermInfoVO.getColumns(), "", bTermInfoVO.getColName(), bTermInfoVO.getCheckFlag()));
                    error.add(ImmutableMap.of("row", i, "errorFields", list));
                }
            }

            if (!data.isEmpty()) {
                // 将合法的记录批量入库
                this.ibTermInfoService.batchInsert(data);
            }

            long time = ((System.currentTimeMillis() - beginTimeMillis));
            ImmutableMap<String, Object> result = ImmutableMap.of(
                    "time", time,
                    "data", data,
                    "error", error
            );
            return new RedpResponse().data(result);
        } catch (Exception e) {
            String message = "导入Excel数据失败," + e.getMessage();
            log.error(message);
            throw new RedpException(message);
        }
    }


    /**
     * 生成 Excel导入模板
     */
    @PostMapping("downloadTemplate")
    public void generateImportTemplate(HttpServletResponse response) throws IOException {


        List<BTermInfoVO> list = new ArrayList<>();
        IntStream.range(0, 1).forEach(i -> {
            BTermInfoVO btermInfoVO = new BTermInfoVO();
            list.add(btermInfoVO);
        });
        // 构建模板
        EasyExcelUtil.export(response, list, "间联终端信息导入模板");

    }

    /*导出模板文件*/
    private void exportExcel(String instId, ExcelOperaUtil eo, String fileName, HttpServletRequest
            request, HttpServletResponse response) {
        OutputStream httpOut = null;
        try {
            /* 获取输出流 */
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setDateHeader("Expires", 0L);
            String header = request.getHeader("User-Agent");
            if (header.toLowerCase().contains("firefox")) {
                BASE64Encoder base64Encoder = new BASE64Encoder();
                response.setHeader("Content-Disposition", "attachment; filename==?UTF-8?B?" + (new String(base64Encoder.encode(fileName.getBytes("UTF-8")))) + "?=");
            } else {
                response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
            }
            httpOut = response.getOutputStream();

            /* 创建excel对象 */
            HSSFWorkbook wb = new HSSFWorkbook();

            createTemplateSheet(instId, eo, wb);

            wb.write(httpOut);
            httpOut.close();

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (httpOut != null) {
                try {
                    httpOut.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public void createTemplateSheet(String instId, ExcelOperaUtil eo, HSSFWorkbook wb) {
        List<ImportExcelColumnAttr> ImportTermInfo = MchntImportExportInitNew.ImportTermInfo;
        HSSFSheet sheet = wb.createSheet("间联终端信息");
        //标题行
        HSSFRow rowTile = sheet.createRow(0);
        //单元格格式对象
        HSSFDataFormat cellFormat = wb.createDataFormat();
        //创建隐藏sheet页存储地区代码信息
        createAreaCdHideSheet(eo, wb);
        //创建隐藏sheet页存储终端厂商、机型
        createBrandAndModelHideSheet(instId, eo, wb);
        //创建隐藏sheet页存储sn号
        createSnHideSheet(instId, eo, wb);

        for (int colIndex = 0; colIndex < ImportTermInfo.size(); colIndex++) {
            ImportExcelColumnAttr colAttr = ImportTermInfo.get(colIndex);
            //单个列标题
            String column = colAttr.getColumnName();
            //列名称
            String key = colAttr.getColumnEname();
//            User user = AuthContextHolder.findAuthUser();

            sheet.setColumnWidth(colIndex, (3000));
            HSSFCell cell = rowTile.createCell(colIndex);
            cell.setCellValue(column);
            String validData = colAttr.getDataformat();
            String[] format = {};
            if (!validData.isEmpty()) {
                format = validData.split(";");
            }
            if (format.length > 0) {
                int funNum = Integer.parseInt(format[0]);
                HSSFDataValidation validateData = null;
                switch (funNum) {
                    case 1://文本框
                        HSSFCellStyle cellStyle1 = wb.createCellStyle();
                        cellStyle1.setDataFormat(cellFormat.getFormat("@"));
                        eo.setCellType((short) 1, (short) 200, (short) colIndex, sheet, cellStyle1);
                        validateData = eo.setValidate((short) 1, (short) 200, (short) colIndex, (short) colIndex, format);
                        break;
                    case 2:    //下拉列表
                        HSSFCellStyle cellStyle2 = wb.createCellStyle();
                        cellStyle2.setDataFormat(cellFormat.getFormat("@"));
                        eo.setCellType((short) 1, (short) 200, (short) colIndex, sheet, cellStyle2);
                        String[] boxVal2 = colAttr.getBoxvalid() == null ? null : colAttr.getBoxvalid().split(";");
                        if (boxVal2 != null) {
                            String[] boxVals2 = new String[6];
                            if (!boxVal2[0].isEmpty()) {
                                if ("fromdb".equals(boxVal2[0])) {//下拉列表从数据库表取
                                    if ("sn".equals(key)) {
                                        boxVals2 = snLst;
                                        validateData = eo.setCheckBoxs((short) 1, (short) 200, (short) colIndex, (short) colIndex, "sn!$A$2:$A$" + snLst.length + 1, format[5]);
                                        break;
                                    }
                                } else if ("special".equals(boxVal2[0])) {
                                    boxVals2 = boxVal2[1].split(",");
                                } else if ("linkage".equals(boxVal2[0])) {//实现联动
                                    if ("termAddrPrvnc".equals(key)) {
                                        boxVals2 = provinces;
                                        validateData = eo.setCheckBoxs((short) 1, (short) 200, (short) colIndex, (short) colIndex, boxVals2, format[5]);
                                    } else if ("termAddrCity".equals(key)) {
                                        for (int r = 1; r <= 200; r++) {
                                            validateData = eo.setCheckBoxs((short) r, (short) r, (short) colIndex, (short) colIndex, "INDIRECT($I$" + (r + 1) + ")", format[5]);
                                            sheet.addValidationData(validateData);
                                        }
                                    } else if ("termAddrArea".equals(key)) {
                                        for (int r = 1; r <= 200; r++) {
                                            validateData = eo.setCheckBoxs((short) r, (short) r, (short) colIndex, (short) colIndex, "INDIRECT($J$" + (r + 1) + ")", format[5]);
                                            sheet.addValidationData(validateData);
                                        }
                                    } else if ("brandCode".equals(key)) {
                                        boxVals2 = brandCodes;
                                        validateData = eo.setCheckBoxs((short) 1, (short) 200, (short) colIndex, (short) colIndex, boxVals2, format[5]);
                                    } else if ("modelCode".equals(key)) {
                                        for (int r = 1; r <= 200; r++) {
                                            validateData = eo.setCheckBoxs((short) r, (short) r, (short) colIndex, (short) colIndex, "INDIRECT($C$" + (r + 1) + ")", format[5]);
                                            sheet.addValidationData(validateData);
                                        }
                                    }
                                    break;
                                } else {
                                    boxVals2 = boxVal2;
                                }
                                validateData = eo.setCheckBoxs((short) 1, (short) 200, (short) colIndex, (short) colIndex, boxVals2, format[5]);
                            }
                        }
                        break;
                    case 3://数字
                        HSSFCellStyle cellStyle3 = wb.createCellStyle();
                        cellStyle3.setDataFormat(cellFormat.getFormat("@"));
                        eo.setCellType((short) 1, (short) 200, (short) colIndex, sheet, cellStyle3);
                        validateData = eo.setValidate((short) 1, (short) 200, (short) colIndex, (short) colIndex, format);
                        break;
                }
                if (validateData != null) {
                    sheet.addValidationData(validateData);
                }
            }
        }
    }

    private void createAreaCdHideSheet(ExcelOperaUtil eo, HSSFWorkbook wb) {
        Map<String, List<String>> siteMap = new HashMap<String, List<String>>();
        Sheet hideSheet = wb.createSheet("areacd");
        wb.setSheetHidden(wb.getSheetIndex(hideSheet), true);

        int rowId = 0;
        // 设置第一行，存省的信息
        Row proviRow = hideSheet.createRow(rowId++);
        proviRow.createCell(0).setCellValue("省列表");
        List<RegionInfo> listPro = regionInfoService.findProvince("01");
        siteMap = getAREA_CD(listPro);
        provinces = getPropertyStr(proviRow, listPro);
        // 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
        Iterator<String> keyIterator = siteMap.keySet().iterator();
        while (keyIterator.hasNext()) {
            String key = keyIterator.next();
            List<String> son = siteMap.get(key);

            Row row = hideSheet.createRow(rowId++);
            row.createCell(0).setCellValue(key);
            for (int i = 0; i < son.size(); i++) {
                Cell cell = row.createCell(i + 1);
                cell.setCellValue(son.get(i));
            }

            // 添加名称管理器
            String range = eo.getRange(1, rowId, son.size());
            Name name = wb.createName();
            name.setNameName(key);
            String formula = "areacd!" + range;
            name.setRefersToFormula(formula);
        }
    }

    private void createBrandAndModelHideSheet(String instId, ExcelOperaUtil eo, HSSFWorkbook wb) {
        Map<String, List<String>> siteMap = new HashMap<String, List<String>>();
        Sheet hideSheet = wb.createSheet("brandCode");
        wb.setSheetHidden(wb.getSheetIndex(hideSheet), true);

        int rowId = 0;
        Row proviRow = hideSheet.createRow(rowId++);
        proviRow.createCell(0).setCellValue("终端厂商列表");
        List<BTermBrandInfo> listBrandCode = ibTermBrandInfoService.getBrandCode(instId);
        siteMap = getTermModels(listBrandCode);
        brandCodes = getPropertyBrandCodeStr(proviRow, listBrandCode);
        // 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
        Iterator<String> keyIterator = siteMap.keySet().iterator();
        while (keyIterator.hasNext()) {
            String key = keyIterator.next();
            List<String> son = siteMap.get(key);

            Row row = hideSheet.createRow(rowId++);
            row.createCell(0).setCellValue(key);
            for (int i = 0; i < son.size(); i++) {
                Cell cell = row.createCell(i + 1);
                cell.setCellValue(son.get(i));
            }

            // 添加名称管理器
            String range = eo.getRange(1, rowId, son.size());
            Name name = wb.createName();
            name.setNameName(key);
            String formula = "brandCode!" + range;
            name.setRefersToFormula(formula);
        }
    }

    private void createSnHideSheet(String instId, ExcelOperaUtil eo, HSSFWorkbook wb) {
        Sheet hideSheet = wb.createSheet("sn");
        wb.setSheetHidden(wb.getSheetIndex(hideSheet), true);
        int rowId = 0;
        Row acqInsCdRow = hideSheet.createRow(rowId++);
        List<BDeviceInfo> listSn = ibDeviceInfoService.getSN(instId);
        snLst = getSnPropertyStr(hideSheet, listSn);
    }

    private String[] getPropertyStr(Row proviRow, List<RegionInfo> list) {
        String[] properties = new String[list.size()];
        for (int i = 0; i < list.size(); i++) {
            String areaInfPro = list.get(i).getRegionName();
            properties[i] = areaInfPro;
            Cell proviCell = proviRow.createCell(i + 1);
            proviCell.setCellValue(areaInfPro);
        }
        return properties;
    }

    private String[] getSnPropertyStr(Sheet hiddenSheet, List<BDeviceInfo> list) {
        String[] properties = new String[list.size()];
        for (int i = 0; i < list.size(); i++) {
            String areaInfPro = list.get(i).getSn();
            properties[i] = areaInfPro;
            Row row = hiddenSheet.createRow(i + 1);
            Cell proviCell = row.createCell(0);
            proviCell.setCellValue(areaInfPro);
        }
        return properties;
    }

    public Map<String, List<String>> getAREA_CD(List<RegionInfo> listPro) {
        Map<String, List<String>> siteMap = new HashMap<String, List<String>>();
        if (listPro.size() > 0) {
            for (int p = 0; p < listPro.size(); p++) {//遍历省
                String areaInfPro = listPro.get(p).getRegionName();
                String areaCdPro = listPro.get(p).getRegionCode();
                List<RegionInfo> listCity = regionInfoService.findCityOrArea(areaCdPro, "02");
                List listC = getXjCity(listCity);
                if (listCity.size() > 0) {
                    for (int c = 0; c < listCity.size(); c++) {//遍历市
                        String areaCdCity = listCity.get(c).getRegionCode();
                        String areainfcity = listCity.get(c).getRegionName();
                        List<RegionInfo> listCout = regionInfoService.findCityOrArea(areaCdCity, "03");
                        List list = getXjArea(listCout);
                        siteMap.put(areainfcity, list);
                    }
                }
                siteMap.put(areaInfPro, listC);
            }
        }

        return siteMap;
    }

    public List getXjCity(List<RegionInfo> cityList) {
        List<String> cityLst = new ArrayList<String>();
        if (cityList.size() > 0) {
            for (int i = 0; i < cityList.size(); i++) {
                String areaInf = cityList.get(i).getRegionName();
                cityLst.add(areaInf);
            }
        }
        return cityLst;
    }

    public List getXjArea(List<RegionInfo> listCout) {
        List<String> areaLst = new ArrayList<String>();
        if (listCout.size() > 0) {
            for (int i = 0; i < listCout.size(); i++) {
                String areaInf = listCout.get(i).getRegionCode() + "-" + listCout.get(i).getRegionName();
                areaLst.add(areaInf);
            }
        }
        return areaLst;
    }

    public Map<String, List<String>> getTermModels(List<BTermBrandInfo> listBrandCode) {
        Map<String, List<String>> siteMap = new HashMap<String, List<String>>();
        for (int p = 0; p < listBrandCode.size(); p++) {
            String brandCode = listBrandCode.get(p).getBrandCode();
            String brandName = brandCode + listBrandCode.get(p).getBrandName();
            List<BTermModelInfo> listTermModel = ibTermModelInfoService.getByBrandCode(brandCode);
            List<String> termModelList = new ArrayList<>();
            if (null != listTermModel && listTermModel.size() > 0) {
                for (BTermModelInfo bTermModelInfo : listTermModel) {
                    termModelList.add(bTermModelInfo.getModelCode() + "-" + bTermModelInfo.getModelName());
                }
                siteMap.put(brandName, termModelList);
            }

        }
        return siteMap;
    }

    private String[] getPropertyBrandCodeStr(Row proviRow, List<BTermBrandInfo> list) {
        String[] properties = new String[list.size()];
        for (int i = 0; i < list.size(); i++) {
            String areaInfPro = list.get(i).getBrandCode() + list.get(i).getBrandName();
            properties[i] = areaInfPro;
            Cell proviCell = proviRow.createCell(i + 1);
            proviCell.setCellValue(areaInfPro);
        }
        return properties;
    }

    private String getCellValue(Row row, int cellNum) {
        Cell cell = row.getCell(cellNum);
        if (cell == null) {
            return null;
        }
        cell.setCellType(Cell.CELL_TYPE_STRING);
        return cell.getStringCellValue();
    }

}
